95

Merging Sheets

95

6.3  ERROR CORRECTION

This is an important step that has a nice error-​correcting feature that you can use in

many other programs. If the target file is not specified, then Cells(2,2) above will be

blank. If it is specified, but is incorrect, then variable x will be blank.

If either of these two values are blank, then it means the code is heading for an

error in subsequent “write” statements and will stop if the developer does not handle

this condition appropriately.

Hence, this condition redirects the code to ask for a file name, starting with the

“MsgBox”.

The loop only executes once, because the variable “filepathtarget” is no longer

false (or blank).

This type of DO…Loop iterative statement always executes at least once, because

the condition check is at the end, unlike the For loop or the While loop.

STEP 3—​main body of the program

  If Right(parentWs.Cells(3, 2), 1) =​ “\” Then

    Folder =​ ActiveWorkbook.Path & “\” & parentWs.Cells(3, 2)

  Else

    Folder =​ ActiveWorkbook.Path & “\” & parentWs.Cells(3, 2) & “\”

    End If

  x =​ Dir(Folder)

 

  If Folder =​ ““ Or x =​ ““ Then

    Call PickFolder(Folder)

  End If

  j =​ 0

  Call GetFolderNames(Folder)

  MsgBox (“Merge Complete. Be sure to save your file “ & filePathTargetFuture)

 

  If Left(parentWs.Cells(6, 2), 1) =​ “Y” Then

    For Each Sheet In fileopenTargetWb.Worksheets

      Sheet.Columns(ColMax +​ 1).EntireColumn.Delete

    Next

  End If

 

    Application.StatusBar =​ False

    fileopenTargetWb.Activate

End Sub

6.4  HEAVY LIFTING

This is the part of the program that does all the heavy lifting.

The “call pickfolder” statement is activated to again do an error correction as in

step 2. It checks if the source folder field was left blank or stated incorrectly. If either

of those conditions is valid, then it presents the user with a dialog box that does not

go away until the user has made a selection of the folder.